<?php
namespace Admin\Controller;
use Admin\Logic\GoodsLogic;
use Think\AjaxPage2;

class ReportController extends BaseController{
	public $begin;
	public $end;
	public $mylimit=8000;
	public function _initialize(){
        parent::_initialize();
		$timegap = I('timegap');
		$timegap = str_replace('+',' ',$timegap);
		if($timegap){
			$gap = explode(' - ', $timegap);
			$begin = $gap[0];
			$end = $gap[1];
		}else{
			$lastweek = date('Y-m-d 00:00',strtotime("-15 day"));//30天前-1 month
			$begin = I('begin',$lastweek);
			$end =  I('end',date('Y-m-d 23:59:59'));
		}
		$this->begin = strtotime($begin);
		$this->end = strtotime($end);//+86399;
	
		$this->assign('timegap',date('Y-m-d H:i:s',$this->begin).' - '.date('Y-m-d H:i:s',$this->end));
	}

	public function index(){
		$now = strtotime(date('Y-m-d'));
        
        $fenbu = M('fenbu')->field('fenbu_id')->select();
        $ids = '';
        foreach ($fenbu as $v){
            $ids.= $v['fenbu_id'].',';
        }
        $ids = substr($ids,0,-1);
		$fenbu_id=I('fenbu_id') !== '' ? I('fenbu_id') : '';
		$fenbu_ids=$fenbu_id !== '' ? $fenbu_id : $ids;

		$today['today_amount'] = M('order')->where("add_time>$this->begin and add_time<$this->end and ufenbu_id in($fenbu_ids) AND order_status in(0,1,2)")->sum('order_amount'); //今日销售总额
		$today['today_order'] = M('order')->where("add_time>$this->begin and add_time<$this->end and ufenbu_id in($fenbu_ids) AND order_status in(0,1,2)")->count(); //今日订单数 
		$today['today_users'] = count(M('order')->where("add_time>$this->begin and add_time<$this->end and ufenbu_id in($fenbu_ids) AND order_status in(0,1,2)")->group('user_id')->select()); //时间下单人数0
		$today['cancel_order'] = M('order')->where("add_time>$this->begin and add_time<$this->end and ufenbu_id in($fenbu_ids) AND order_status=3")->count();//今日取消订单
		$today['sign'] = sanwei($today['today_amount']/$today['today_order']);
		$this->assign('today',$today);
		//$sql = "SELECT COUNT(order_id) as tnum,sum(order_amount) as amount,sum(kou_price) as kou_price, FROM_UNIXTIME(add_time,'%Y-%m-%d') as gap from  __PREFIX__order where add_time>$this->begin and add_time<$this->end and ufenbu_id in($fenbu_ids) AND order_status in(0,1,2) group by gap ";
		//$res = M()->cache(true,18000)->query($sql);
		
		$res =M("order")
		    ->field("COUNT(order_id) as tnum,sum(order_amount) as amount,sum(kou_price) as kou_price, FROM_UNIXTIME(add_time,'%Y-%m-%d') as gap")
		    ->where("add_time>=$this->begin and add_time<=$this->end and ufenbu_id in($fenbu_ids) AND (order_status in(0,1,2))")
		    ->group("gap")
		    ->cache(true,3600)
		    ->select();
		//订单数,交易额
// 		echo $this->begin.'+'.$this->end;
		
// exit;
		foreach ($res as $val){
			$arr[$val['gap']] = $val['tnum'];
			$brr[$val['gap']] = $val['amount'];
			$drr[$val['gap']] = $val['kou_price'];
			$end_time=strtotime($val['gap'])+24*3600-1;
			$crr[$val['gap']] = count(M('order')->field('user_id')->where("add_time>".strtotime($val['gap'])." and add_time<".$end_time." and ufenbu_id in($fenbu_ids) and (order_status in(0,1,2))")->group('user_id')->limit($this->mylimit)->select());
			$tnum += $val['tnum'];
			$tamount += $val['amount'];
		}

		for($i=$this->end;$i>=$this->begin;$i=$i-24*3600){
			$tmp_num = empty($arr[date('Y-m-d',$i)]) ? 0 : $arr[date('Y-m-d',$i)];
			$tmp_amount = empty($brr[date('Y-m-d',$i)]) ? 0 : $brr[date('Y-m-d',$i)];
			$tmp_kou_price = empty($drr[date('Y-m-d',$i)]) ? 0 : $drr[date('Y-m-d',$i)];
			$tmp_users = empty($crr[date('Y-m-d',$i)]) ? 0 : $crr[date('Y-m-d',$i)];
			$tmp_sign = empty($tmp_num) ? 0 : sanwei($tmp_amount/$tmp_num);
			$order_arr[] = $tmp_num; 
			$amount_arr[] = $tmp_amount;
			$users_num[]=$tmp_users;
			$sign_arr[] = $tmp_sign; 
			$date = date('Y-m-d',$i);
			$list[] = array('day'=>$date,'order_num'=>$tmp_num,'amount'=>$tmp_amount,'bili'=>geshi($tmp_kou_price/$tmp_amount)*100,'kou_price'=>$tmp_kou_price,'users_num'=>$tmp_users,'sign'=>$tmp_sign,'end'=>date('Y-m-d',$i+24*60*60));
			$day[] = $date;
		}
		$last_names = array_column($list,'day');
		array_multisort($last_names,SORT_DESC,$list); 
		
		//$zhongjine=M()->query('SELECT SUM(total_amount) AS tp_sum FROM `ty_order` WHERE order_status in(0,1,2,4) and ufenbu_id='.$fenbu_id);
		$zongjine = M('order')->where("order_status in(0,1,2) and ufenbu_id in($fenbu_ids)")->cache(true,18000)->sum('total_amount');
		$zongjine2 = M('order')->where("order_status in(0,1,2) and add_time>$this->begin and add_time<$this->end and ufenbu_id in($fenbu_ids)")->cache(true,18000)->sum('total_amount');
		$koudian = M('order')->where("order_status in(0,1,2) and ufenbu_id in($fenbu_ids)")->cache(true,18000)->sum('kou_price');
		$koudian2 = M('order')->where("order_status in(0,1,2) and add_time>$this->begin and add_time<$this->end and ufenbu_id in($fenbu_ids)")->cache(true,18000)->sum('kou_price');
		$this->assign('list',$list);$this->assign('fenbu_list',$this->fenbu_list);
		$this->assign('zongdan',M('order')->where("order_status in(0,1,2) and ufenbu_id in($fenbu_ids)")->cache(true,18000)->count());
		$this->assign('zongjine',$zongjine);
		$this->assign('koudian',$koudian);
		$this->assign('koudianbi',geshi($koudian/$zongjine)*100);
		
		$this->assign('zongjine2',$zongjine2);
		$this->assign('koudian2',$koudian2);
		$this->assign('koudianbi2',geshi($koudian2/$zongjine2)*100);
		$result = array('order'=>$order_arr,'amount'=>$amount_arr,'sign'=>$sign_arr,'time'=>$day);
		$this->assign('result',json_encode($result));
		$this->display();
	}
    
       public function liulangoodsList(){
    
        $list = M('goods g')
        ->field('g.goods_id, count(l.goods_id) AS counts,g.goods_name')
        ->join('ty_goods_liulan l ON l.goods_id = g.goods_id')
        ->where("l.add_time > {$this->begin} AND l.add_time < {$this->end}")
        ->group('g.goods_id')
        ->order('counts DESC')
        ->limit(200)
        ->select();;
        // dump($list);
        $this->assign('list',$list);
        $this->display();         
    }  
    public function liulanstoreList(){
    
        $list = M('store s')
        ->field('s.store_id, count(l.store_id) AS counts,s.store_name')
        ->join('ty_store_liulan l ON l.store_id = s.store_id')
        ->where("l.add_time > {$this->begin} AND l.add_time < {$this->end} and l.store_id !='' ")
        ->group('s.store_id')
        ->order('counts DESC')
        ->limit(200)
        ->select();;
        // dump($list);
        $this->assign('list',$list);
        $this->display();         
    } 
    public function liulan(){
        //获取商品浏览量
        $goods_count = M('goods_liulan')->where("user_id != 61")->count();
        $store_count = M('store_liulan')->where("user_id != 61 and store_id != '' ")->count();
        $index_count = M('store_liulan')->where("user_id != 61 and store_id is NULL ")->count();
        
        
        $goods_count_now = M('goods_liulan')->where("add_time>$this->begin and add_time<$this->end and user_id != 61")->count();
        $store_count_now = M('store_liulan')->where("add_time>$this->begin and add_time<$this->end and user_id != 61 and store_id != ''")->count();
        $index_count_now = M('store_liulan')->where("add_time>$this->begin and add_time<$this->end and user_id != 61 and store_id is NULL")->count();
        
        $data = [];
        $data[0] = ['value'=>$index_count_now,'name'=>'首页'];
        $data[1] = ['value'=>$goods_count_now,'name'=>'商品'];
        $data[2] = ['value'=>$store_count_now,'name'=>'店铺'];
      
        $this->assign('goods_count',$goods_count);
        $this->assign('store_count',$store_count);
        $this->assign('index_count',$index_count);
        $this->assign('data',json_encode($data));
        $this->assign('datas',$data);
        $this->display();
    }
    
    public function expor_index(){
		$now = strtotime(date('Y-m-d'));
        $fenbu = M('fenbu')->field('fenbu_id')->select();
        $ids = '';
        foreach ($fenbu as $v){
            $ids.= $v['fenbu_id'].',';
        }
        $ids = substr($ids,0,-1);
		$fenbu_id=I('fenbu_id') !== '' ? I('fenbu_id') : '';
		$fenbu_ids=$fenbu_id !== '' ? $fenbu_id : $ids;
		//$sql = "SELECT COUNT(order_id) as tnum,sum(order_amount) as amount,sum(kou_price) as kou_price, FROM_UNIXTIME(add_time,'%Y-%m-%d') as gap from  __PREFIX__order where add_time>$this->begin and add_time<$this->end and ufenbu_id in($fenbu_ids) AND order_status in(0,1,2) group by gap ";
		//$res = M()->cache(true,18000)->query($sql);
		
		$res =M("order")
		    ->field("COUNT(order_id) as tnum,sum(order_amount) as amount,sum(kou_price) as kou_price, FROM_UNIXTIME(add_time,'%Y-%m-%d') as gap")
		    ->where("add_time>=$this->begin and add_time<=$this->end and ufenbu_id in($fenbu_ids) AND (order_status in(0,1,2))")
		    ->group("gap")
		    ->cache(true,3600)
		    ->select();
		//订单数,交易额
// 		echo $this->begin.'+'.$this->end;
		
// exit;
		foreach ($res as $val){
			$arr[$val['gap']] = $val['tnum'];
			$brr[$val['gap']] = $val['amount'];
			$drr[$val['gap']] = $val['kou_price'];
			$end_time=strtotime($val['gap'])+24*3600-1;
			$crr[$val['gap']] = count(M('order')->field('user_id')->where("add_time>".strtotime($val['gap'])." and add_time<".$end_time." and ufenbu_id in($fenbu_ids) and (order_status in(0,1,2))")->group('user_id')->limit($this->mylimit)->select());
			$tnum += $val['tnum'];
			$tamount += $val['amount'];
		}

		for($i=$this->end;$i>=$this->begin;$i=$i-24*3600){
			$tmp_num = empty($arr[date('Y-m-d',$i)]) ? 0 : $arr[date('Y-m-d',$i)];
			$tmp_amount = empty($brr[date('Y-m-d',$i)]) ? 0 : $brr[date('Y-m-d',$i)];
			$tmp_kou_price = empty($drr[date('Y-m-d',$i)]) ? 0 : $drr[date('Y-m-d',$i)];
			$tmp_users = empty($crr[date('Y-m-d',$i)]) ? 0 : $crr[date('Y-m-d',$i)];
			$tmp_sign = empty($tmp_num) ? 0 : sanwei($tmp_amount/$tmp_num);
			$order_arr[] = $tmp_num; 
			$amount_arr[] = $tmp_amount;
			$users_num[]=$tmp_users;
			$sign_arr[] = $tmp_sign; 
			$date = date('Y-m-d',$i);
			$list[] = array('day'=>$date,'order_num'=>$tmp_num,'amount'=>$tmp_amount,'bili'=>geshi($tmp_kou_price/$tmp_amount)*100,'kou_price'=>$tmp_kou_price,'users_num'=>$tmp_users,'sign'=>$tmp_sign,'end'=>date('Y-m-d',$i+24*60*60));
			$day[] = $date;
		}
		$last_names = array_column($list,'day');
		array_multisort($last_names,SORT_DESC,$list); 
		
		
	    $strTable ='<table width="500" border="1">';
    	$strTable .= '<tr>';
    	$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">时间</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="100">订单数</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">下单人数</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">客单价</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">销售总额</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">扣点比例</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">扣点收入</td>';
    	$strTable .= '</tr>';
    	
		foreach($list as $k=>$val){
    		$strTable .= '<tr>';
    		$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['day'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['order_num'].' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['users_num'].' </td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['sign'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">￥'.$val['amount'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['bili'].'%</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['kou_price'].'</td>';
    		$strTable .= '</tr>';
    	}
    	$strTable .='</table>';
    	unset($orderList);
    	downloadExcel($strTable,'销售概况');
    	exit();
    }
    
    /*
    满减
    */
    public function fullReduction(){
        $page = I('p',1);
        $limit = I('limit',20);
        if($page == 1){
            $page = 0;
        }else{
            $page = $page-1;
        }
        $showPage = $page*$limit;
        $where="a.add_time>$this->begin and a.add_time<$this->end";
        $biaoshi_xian = I('biaoshi_xian');
        $name = I('name');
        if(!empty($biaoshi_xian)){
            $where .= ' and u.biaoshi_xian = "'.$biaoshi_xian.'"';
        }
        if(!empty($name)){
            $where .= ' and (u.nickname like "%'.$name.'%" or u.company like "&'.$name.'&" )';
        }
        $list = M('users_price a')
        ->field('sum(a.price) as sumprice,a.*,u.*')
        ->join('ty_users u on u.user_id = a.user_id')
        ->where($where)
        ->group('a.user_id')
        //->limit($showPage,$limit)
        ->order('sumprice desc,a.user_id')
        ->select();
//         $count = M('users_price a')
//         ->field('sum(a.price) as sumprice,a.*,u.*')
//         ->join('ty_users u on u.user_id = a.user_id')
//         ->where($where)
//         ->group('a.user_id')
//         ->order('sumprice desc,a.user_id')
//         ->select();
        $data = tpCache('full_reduction');
        $full = json_decode($data['full'],true);
        usort($full, function ($a, $b) {
            return $a['price'] - $b['price'];
        });
        foreach ($list as $k=>$v){
            $list[$k]['gift'] = '';
            foreach($full as $key=>$val){
                if($v['sumprice'] > $val['price']){
                    $list[$k]['gift'] = $val['gift'];
                }
            }
        }
        $price_heji = 0;
        foreach ($list as $k=>$v){
            $price_heji+=$v['sumprice'];
        }
        $count = \count($list);
		$Page = new \Think\Page($count,20);
		$show = $Page->show();
        $this->assign('list',$list);
        $this->assign('price_heji',geshi($price_heji));
        $this->assign('page',$show);
        $this->display();
    }
    
    public function exportfullReduction(){
        $biaoshi_xian = I('biaoshi_xian');
        $name = I('name');
        $where="a.add_time>$this->begin and a.add_time<$this->end";
        if(!empty($biaoshi_xian)){
            $where .= ' and u.biaoshi_xian = "'.$biaoshi_xian.'"';
        }
        if(!empty($name)){
            $where .= ' and (u.nickname like "%'.$name.'%" or u.company like "&'.$name.'&" )';
        }
      
        $count = M('users_price a')
        ->field('sum(a.price) as sumprice,a.*,u.*')
        ->join('ty_users u on u.user_id = a.user_id')
        ->where($where)
        ->group('a.user_id')
        ->order('sumprice desc,a.user_id')
        ->select();
        $data = tpCache('full_reduction');
        $full = json_decode($data['full'],true);
        usort($full, function ($a, $b) {
            return $a['price'] - $b['price'];
        });
        
        $strTable ='<table width="500" border="1">';
    	$strTable .= '<tr>';
    	$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">用户id</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="100">线路标识</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">客户名</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">联系电话</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">客户地址</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">购物金额</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">礼物</td>';
    	$strTable .= '</tr>';
    	
    		foreach($count as $k=>$val){
        	    $val['gift'] = '';
                foreach($full as $key=>$v){
                    if($val['sumprice'] > $v['price']){
                        $val['gift'] = $v['gift'];
                    }
                }
    		$strTable .= '<tr>';
    		$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['user_id'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['biaoshi_xian'].' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['nickname'].'（'.$val['company'].'）'.' </td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['mobile'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['company_address'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['sumprice'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['gift'].'</td>';
    		$strTable .= '</tr>';
    	}
    	$strTable .='</table>';
    	unset($orderList);
    	downloadExcel($strTable,'满减');
    	exit();
    	
    	
    
       
    }
    
    
    public function yanzhiorder(){
       
        $page = I('p',1);
        $limit = I('limit',20);
        if($page == 1){
            $page = 0;
        }else{
            $page = $page-1;
        }
        $where="a.add_time>$this->begin and a.add_time<$this->end";
        $biaoshi_xian = I('biaoshi_xian');
        $name = I('name');
        if(!empty($biaoshi_xian)){
            $where .= ' and u.biaoshi_xian = "'.$biaoshi_xian.'"';
        }
        if(!empty($name)){
            $where .= ' and (u.nickname like "%'.$name.'%" or u.company like "&'.$name.'&" )';
        }
        $showPage = $page*$limit;
        $res = M('order_yanzhi a')
        ->join('ty_order o on o.order_id = a.order_id',"LEFT")
        ->join('ty_users u on u.user_id = o.user_id')
        ->join('ty_goods g on g.goods_id = a.goods_id')
        ->where($where)
        ->limit($showPage,$limit)
        ->select();
        foreach ($res as $k=>$v){
            
            if($v['order_status'] == 0){
                $res[$k]['order_status'] = '待确认';
            }elseif($v['order_status'] == 1){
                $res[$k]['order_status'] = '已确认';
            }elseif($v['order_status'] == 2){
                $res[$k]['order_status'] = '已完成';
            }elseif($v['order_status'] == 3){
                $res[$k]['order_status'] = '取消';
            }elseif($v['order_status'] == 4){
                $res[$k]['order_status'] = '作废';
            }
            
            if($v['driver_id'] > 0){
                $drier = M('users')->where(['user_id'=>$v['driver_id']])->getField('nickname');
            }else{
                $drier = '无';
            }
            
            //查询是否有退货
            $return_goods = M('return_goods')->where(['order_id'=>$v['order_id'],'goods_id'=>$v['goods_id'],'status'=>2,'spec_key'=>$v['key']])->find();
            
            // if(!empty($return_goods)){
            //     $res[$k]['goods_num'] = $v['goods_num']-$return_goods['goods_num'];
            // }
            $res[$k]['koudian'] = ($v['price']*($v['points']/100))*$res[$k]['goods_num'];
            $res[$k]['price_zong'] = geshi($v['price']*$res[$k]['goods_num']);
            $res[$k]['drier'] = $drier;
            $res[$k]['tuihuo_price'] = $return_goods['goods_num']*$return_goods['member_goods_price'];
            
        }
        $count = M('order_yanzhi a')
        ->join('ty_order o on o.order_id = a.order_id',"LEFT")
        ->join('ty_users u on u.user_id = o.user_id')
        ->join('ty_goods g on g.goods_id = a.goods_id')
        ->where($where)
        ->select();
        $price_heji = $koudian = 0;
        foreach ($count as $k=>$v){
             $return_goods = M('return_goods')->where(['order_id'=>$v['order_id'],'goods_id'=>$v['goods_id'],'status'=>2])->find();
            if(!empty($return_goods)){
                // dump($v);
                $v['goods_num'] = $v['goods_num']-$return_goods['goods_num'];
            }
            $price_heji+=$v['price']*$v['goods_num'];
            $koudian+= ($v['price']*($v['points']/100))*$v['goods_num'];;
        }
    	$count = \count($count);
		$Page = new \Think\Page($count,20);
		$show = $Page->show();
        $this->assign('list',$res);
        $this->assign('page',$show);
        $this->assign('price_heji',geshi($price_heji));
        $this->assign('koudian',geshi($koudian));
        $this->display();
    }
    public function export_yanzhi(){
        $where="a.add_time>$this->begin and a.add_time<$this->end";
        $biaoshi_xian = I('biaoshi_xian');
        $name = I('name');
        if(!empty($biaoshi_xian)){
            $where .= ' and u.biaoshi_xian = "'.$biaoshi_xian.'"';
        }
        if(!empty($name)){
            $where .= ' and (u.nickname like "%'.$name.'%" or u.company like "&'.$name.'&" )';
        }
        $res = M('order_yanzhi a')
        ->join('ty_order o on o.order_id = a.order_id',"LEFT")
        ->join('ty_users u on u.user_id = o.user_id')
        ->join('ty_goods g on g.goods_id = a.goods_id')
        ->where($where)
        ->select();
        
        $strTable ='<table width="500" border="1">';
    	$strTable .= '<tr>';
    	$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">商品名称</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="100">线路标识</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">客户名</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">配送员</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">联系电话</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">客户地址</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">购物单价</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">购物数量</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">订单总金额</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">扣点收益</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">退货金额</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">订单状态	</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">订单号</td>';
    	$strTable .= '</tr>';
    	
    		foreach($res as $k=>$val){
        	    
                if($val['order_status'] == 0){
                    $val['order_status'] = '待确认';
                }elseif($val['order_status'] == 1){
                    $val['order_status'] = '已确认';
                }elseif($val['order_status'] == 2){
                    $val['order_status'] = '已完成';
                }elseif($val['order_status'] == 3){
                    $val['order_status'] = '取消';
                }elseif($val['order_status'] == 4){
                    $val['order_status'] = '作废';
                }
                
                if($val['driver_id'] > 0){
                    $drier = M('users')->where(['user_id'=>$val['driver_id']])->getField('nickname');
                }else{
                    $drier = '无';
                }
                
                 //查询是否有退货
            $return_goods = M('return_goods')->where(['order_id'=>$val['order_id'],'goods_id'=>$val['goods_id'],'status'=>2,'spec_key'=>$val['key']])->find();
         
                $val['tuihuo_price'] = $return_goods['goods_num']*$return_goods['member_goods_price'];
            
            
                $val['koudian'] = ($val['price']*($val['points']/100))*$val['goods_num'];
                $val['price_zong'] = geshi($val['price']*$val['goods_num']);
    		$strTable .= '<tr>';
    		$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['goods_name'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['biaoshi_xian'].' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['nickname'].'（'.$val['company'].'）'.' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$drier.'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['mobile'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['company_address'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['price'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['goods_num'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['price_zong'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['koudian'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['tuihuo_price'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['order_status'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">&nbsp;'.$val['order_sn'].'</td>';
    		$strTable .= '</tr>';
    	}
    	$strTable .='</table>';
    	unset($orderList);
    	downloadExcel($strTable,'特推好货');
    	exit();
    }
    
    
	public function saleTop(){
        $goods_name=I('goods_name') ? I('goods_name') : '';
        $where = '1=1';
        if(!empty($goods_name)){
            $where = ' a.goods_name like "%'.$goods_name.'%"';
        }

        if(I('fenbu_id') !== ''){
            $fenbu_id=I('fenbu_id') ? I('fenbu_id') : \session('fenbu_id');
            $where.=" and b.ufenbu_id=$fenbu_id";
        }

        $where .=" and b.order_status in(0,1,2,4) and b.add_time>$this->begin and b.add_time<$this->end ";

// 		$sql = "select a.goods_id,a.goods_name,a.goods_sn,sum(a.goods_num) as sale_num,sum(a.goods_num*a.member_goods_price) as sale_amount,b.add_time from ty_order_goods a left join ty_order b on a.order_id=b.order_id";
// 		$sql .=" $where group by a.goods_id order by sale_amount DESC limit 200";
// 		$res = M()->cache(true,3600)->query($sql);
		$res = M('order_goods a')
		->field('a.goods_id,a.goods_name,a.goods_sn,sum(a.goods_num) as sale_num,sum(a.goods_num*a.member_goods_price) as sale_amount,b.add_time')
		->join('ty_order b on a.order_id=b.order_id','LEFT')
		->where($where)
		->group('a.goods_id')
		->order('sale_amount desc')
		->limit(200)
		->select();

		foreach($res as $key=>$v){
			$store_id=M('goods')->where('goods_id='.$v['goods_id'])->getField('store_id');
			if($store_id)
			$res[$key]['store_name']=M('store')->where('store_id='.$store_id)->getField('store_name');
		}
		$this->assign('fenbu_list',$this->fenbu_list);
		$this->assign('list',$res);
		$this->display();
	}
	    
    public function exportSaleTops(){
        $goods_name=I('goods_name') ? I('goods_name') : '';
        $where = '1=1';
        if(!empty($goods_name)){
            $where = ' a.goods_name like "%'.$goods_name.'%"';
        }

        if(I('fenbu_id') !== ''){
            $fenbu_id=I('fenbu_id') ? I('fenbu_id') : \session('fenbu_id');
            $where.=" and b.ufenbu_id=$fenbu_id";
        }

        $where .=" and b.order_status in(0,1,2,4) and b.add_time>$this->begin and b.add_time<$this->end ";
        // $where .=' and   u.b1=89';
        // dump($where);
        // $sql = "select c.name as cname,g.cat_id1,g.cat_id2,a.goods_id,a.goods_name,a.goods_sn,sum(a.goods_num) as sale_num,sum(a.goods_num*a.member_goods_price) as sale_amount,b.add_time from ty_order_goods a left join ty_order b on a.order_id=b.order_id left join ty_goods g on g.goods_id=a.goods_id left join ty_goods_category c on c.id=g.cat_id3 left join ty_users u on u.user_id=b.user_id ";
        // $sql = "select c.name as cname,g.cat_id1,g.cat_id2,a.goods_id,a.goods_name,a.goods_sn,sum(a.goods_num) as sale_num,sum(a.goods_num*a.member_goods_price) as sale_amount,b.add_time from ty_order_goods a left join ty_order b on a.order_id=b.order_id left join ty_goods g on g.goods_id=a.goods_id left join ty_goods_category c on c.id=g.cat_id3  ";
        // $sql .=" $where group by a.goods_id order by sale_amount DESC limit 100";
        // $res = M()->cache(true,3600)->query($sql);
        $res = M('order_goods a')
		->field('a.goods_id,a.goods_name,a.goods_sn,sum(a.goods_num) as sale_num,sum(a.goods_num*a.member_goods_price) as sale_amount,b.add_time')
		->join('ty_order b on a.order_id=b.order_id','LEFT')
		->where($where)
		->group('a.goods_id')
		->order('sale_amount desc')
		->limit(200)
		->select();

        foreach($res as $key=>$v){
            $store_id=M('goods')->where('goods_id='.$v['goods_id'])->getField('store_id');
            if($store_id)
                $res[$key]['store_name']=M('store')->where('store_id='.$store_id)->getField('store_name');
        }
        $strTable ='<table width="500" border="1">';
    	$strTable .= '<tr>';
		$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">排行</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">商品名称</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">货号</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">所属店铺</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">销售量</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">销售额</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">均价</td>';
    	$strTable .= '</tr>';
    	foreach($res as $k=>$val){
		    $i = $k+1;
    		$strTable .= '<tr>';
			$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$i.'</td>';
    		$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['goods_name'].'</td>';
			$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['goods_sn'].'</td>';
			$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['store_name'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['sale_num'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['sale_amount'].' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.round($val['sale_amount']/$val['sale_num'],2).' </td>'; 
    		$strTable .= '</tr>';
    	}
    	$strTable .='</table>';
    	unset($res);
    	downloadExcel($strTable,'联鑫壹天达单品销售排行');
    	exit();
    	
    }
	
	public function userTop(){
		$p = I('p',1);
		$start = ($p-1)*20;
		$mobile = I('mobile');
		$kehu = I('kehu');
		$company_address = I('company_address');
		$order_type=I('order_type');
		if($order_type){
			if($order_type=='1'){
				$where = "a.dai_type = 0 and ";
			}elseif($order_type=='2'){
				$where = "a.dai_type = 1 and ";
			}
		}
		// $fenbu_id=I('fenbu_id') ? I('fenbu_id') : \session('fenbu_id');

        $where .= " a.order_status in(0,1,2,4)";
		$whereb="a.addtime>$this->begin and a.addtime<$this->end and b.is_lock=0";
		if(I('fenbu_id')!=''){
			$fenbu_id=I('fenbu_id');
			$where .= " and b.fenbu_id=$fenbu_id";
			$whereb .= " and b.fenbu_id=$fenbu_id";
		}
		if($company_address){
            $where .=  " and b.company_address like '%$company_address%' ";
            $whereb.=" and b.company_address like '%$company_address%' ";
        }
		if($mobile){
			//$where .=  " and (b.mobile like '%$mobile%' or b.biaoshi_xian = '$mobile') ";
			//$whereb.=" and (b.mobile like '%$mobile%' or b.biaoshi_xian = '$mobile') ";
			$where .=  " and b.biaoshi_xian = '$mobile' ";
			$whereb.=" and b.biaoshi_xian = '$mobile' ";
		}
		if($kehu){
			$where .= " and (b.company like'%$kehu%' or b.nickname like'%$kehu%') ";
			$whereb.=" and (b.company like'%$kehu%' or b.nickname like'%$kehu%') ";
		}
		$whereb .= " and a.status!=3";
		$num = I('num',20);
// 		$res = M()->cache(true)->query("select a.add_time,count(a.order_id) as order_num,sum(a.order_amount+a.yijia_heji) as amount,b.biaoshi,b.biaoshi_xian,a.user_id,b.mobile,b.company,b.nickname,b.user_id,b.company_address from __PREFIX__order as a left join __PREFIX__users as b on a.user_id = b.user_id where a.add_time>$this->begin and a.add_time<$this->end and $where group by a.user_id order by amount DESC limit $start,$num");
		$res = M('order a')
		
		->field('a.add_time,count(a.order_id) as order_num,sum(a.total_amount+a.yijia_heji) as amount,b.biaoshi,b.biaoshi_xian,a.user_id,b.mobile,b.company,b.nickname,b.user_id,b.company_address')
		->join('ty_users b on a.user_id = b.user_id','LEFT')
		->where(" a.add_time>$this->begin and a.add_time<$this->end and".$where)
		->group('a.user_id')
		->order('amount desc')
		->limit($start,$num)
		->select();
	$total = M('order a')
	    
		->field('a.add_time,count(a.order_id) as order_num,sum(a.total_amount+a.yijia_heji) as amount,b.biaoshi,b.biaoshi_xian,a.user_id,b.mobile,b.company,b.nickname,b.user_id,b.company_address')
		->join('ty_users b on a.user_id = b.user_id','LEFT')
		->where(" a.add_time>$this->begin and a.add_time<$this->end and".$where)
		->group('a.user_id')
		->order('amount desc')
		->select();
// 		$total = M()->cache(true)->query("select a.add_time,count(a.order_id) as order_num,sum(a.order_amount+a.yijia_heji) as amount,b.biaoshi,b.biaoshi_xian,a.user_id,b.mobile,b.company,b.nickname,b.user_id from __PREFIX__order as a left join __PREFIX__users as b on a.user_id = b.user_id where a.add_time>$this->begin and a.add_time<$this->end and $where group by a.user_id order by amount DESC");
		
// 		$total2 = M('order')->cache(true)->field('order_amount,shipping_status')->where("add_time>$this->begin and add_time<$this->end and order_status in(0,1,2,4)")->select();
//     	dump($this->begin);
// 		dump($this->end);
		$price_heji=$tuikuans=$tuikuans3=$tuikuans4=$tuikuans1=$notfahuo=$notfahuo2=0;
		$tuikuan=M('return_goods a')
		
		->field("SUM(((a.member_goods_price+a.yijia)*a.goods_num)) as sum,a.*,b.company,b.nickname,b.mobile,b.biaoshi_xian")
		->join('ty_users b on a.user_id=b.user_id')
		->where($whereb)->select();
		foreach($tuikuan as $v){
			$tuikuans+=$v['sum'];
			if($v['status'] == 2){
			    $tuikuans3+=($v['member_goods_price']+$v['yijia'])*$v['goods_num'];
			}
			if($v['status'] == 1){
			    $tuikuans1+=($v['member_goods_price']+$v['yijia'])*$v['goods_num'];
			}
		}
		
		$result = [];
		$tuikuan_data=M('return_goods a')
		->field("a.member_goods_price,a.goods_num,a.yijia,a.user_id")
		->join('ty_users b on a.user_id=b.user_id')
		->where($whereb)
		->select();
		foreach ($tuikuan_data as $key =>$item) {
		    $result[$item["user_id"]][$key]['goods_num'] = $item['goods_num'];
		    $result[$item["user_id"]][$key]['member_goods_price'] = $item['member_goods_price'];
		}
		
		foreach($total as $v){
			$price_heji+=$v['amount'];
// 			 $tuikuan2=M('return_goods a')
			
//                 ->field("a.member_goods_price,a.goods_num,a.yijia")
//                 ->join('ty_users b on a.user_id=b.user_id')
//                 ->where($whereb.' and a.user_id = '.$v['user_id'])
//                 // ->where(['a.user_id'=>$value['user_id'],'a.status'=>2])
//                 ->select();
//            dump($tuikuan2);
            $return_goods_price = 0;
            foreach ($result[$v['user_id']] as $key => $value){
                $return_goods_price += ($value['member_goods_price']+$v['yijia'])*$value['goods_num'];
            }
            $shiji += $v['amount']-$return_goods_price;
            $tuikuans4 += $return_goods_price;
		}
		foreach ($total2 as $v){
		    if($v['shipping_status'] == 0){
		        $notfahuo += $v['order_amount'];
		    }
		    if($v['shipping_status'] == 1){
		        $notfahuo2 += $v['order_amount'];
		    }
		}
//        dump($whereb);
		foreach ($res as $k=>$v){
            // $whereb .= ' and a.user_id = '.$v['user_id'].' and a.status = 2';
            $tuikuan2=M('return_goods a')
            
                ->field("SUM(((a.member_goods_price+a.yijia)*a.goods_num)) as sum")
                ->join('ty_users b on a.user_id=b.user_id')
                ->where($whereb.' and a.user_id = '.$v['user_id'])
                // ->where(['a.user_id'=>$value['user_id'],'a.status'=>2])
                ->select();
            // dump($tuikuan2);
            $return_goods_price = 0;
            foreach ($tuikuan2 as $key => $value){
                $return_goods_price += $value['sum'];
            }
            $v['return_goods_price'] = $return_goods_price;
            $v['shiji'] = geshi($v['amount']-$v['return_goods_price']);
            $res[$k] = $v;
        }
        usort($res, function($a, $b) {
            return $b['shiji'] <=> $a['shiji'];
        });
//        dump($res);
		$count = \count($total);
		$Page = new \Think\Page2($count);
		$show = $Page->show();
		$this->assign('page',$show);$this->assign('fenbu_list',$this->fenbu_list);
		$this->assign('price_heji',geshi($price_heji));
		$this->assign('tuikuans',geshi($tuikuans));
		$this->assign('tuikuans3',geshi($tuikuans3));
		$this->assign('tuikuans1',geshi($tuikuans1));
		$this->assign('notfahuo',geshi($notfahuo));
		$this->assign('notfahuo2',geshi($notfahuo2));
		$this->assign('list',$res);
		$this->assign('shiji',geshi($shiji));
		$this->assign('tuikuans4',geshi($tuikuans4));
		$this->display();
	}
	
public function userTop_xian(){
		$myarry=[];$price_heji=0;
		if(session('fenbu_id')!=0){
			$wheres=' and b.fenbu_id='.\session('fenbu_id');
		}

        function group_same_key_sofwu($arr,$key){
            $new_arr = array();
            foreach($arr as $k=>$v ){
                $new_arr[$v[$key]] +=$v['amount'];
            }
            return $new_arr;
        }

        $total = M()->query("select (a.order_amount+a.yijia_heji) as amount, b.biaoshi_xian from __PREFIX__order as a left join __PREFIX__users as b on a.user_id = b.user_id where  a.chuku_last_time>$this->begin and a.chuku_last_time<$this->end and a.order_status in(0,1,2,4)  $wheres");
        $total = group_same_key_sofwu($total,'biaoshi_xian');
        ksort($total, SORT_NATURAL);


        $tuihuo_total = M()->query("select ROUND(((a.member_goods_price+a.yijia) * a.goods_num),3) as amount,b.biaoshi_xian from __PREFIX__return_goods as a left join __PREFIX__users as b on a.user_id = b.user_id where  a.addtime>$this->begin and a.addtime<$this->end  $wheres");
        $tuihuo_total = group_same_key_sofwu($tuihuo_total,'biaoshi_xian');
        ksort($tuihuo_total, SORT_NATURAL);

//        $myarry = M('users')->field('biaoshi_xian')->where("biaoshi_xian != '' ")->order('CAST(biaoshi_xian AS UNSIGNED) ASC')->group('biaoshi_xian')->select();
//
//        foreach ($myarry as $k=>$v){
//            $total = M()->cache(true,TPSHOP_CACHE_TIME)->query("select sum(a.order_amount+a.yijia_heji) as amount from __PREFIX__order as a left join __PREFIX__users as b on a.user_id = b.user_id where  a.add_time>$this->begin and a.add_time<$this->end and a.order_status in(0,1,2,4) and b.biaoshi_xian='".$v['biaoshi_xian']."' $wheres group by b.biaoshi_xian having sum(a.order_amount) > 0");
//            $tuihuo_total = M()->cache(true,TPSHOP_CACHE_TIME)->query("select ROUND(sum((a.member_goods_price+a.yijia) * a.goods_num),3) as amount from __PREFIX__return_goods as a left join __PREFIX__users as b on a.user_id = b.user_id where  a.addtime>$this->begin and a.addtime<$this->end and b.biaoshi_xian='".$v['biaoshi_xian']."' $wheres group by b.biaoshi_xian having sum((a.member_goods_price+a.yijia) * a.goods_num) > 0");
//            if($total[0]['amount'] != '' && $total[0]['amount'] > 0){
//                $myarry[$k]['price_heji'] = $total[0]['amount'];
//                $myarry[$k]['tuihuo_heji'] = $tuihuo_total[0]['amount'];
//                $myarry[$k]['heji'] = $total[0]['amount'] - $tuihuo_total[0]['amount'];
//                $price_heji+=$myarry[$k]['heji'];
//            }else{
//                unset($myarry[$k]);
//            }
//        }
//        $myarry = array_values($myarry);
//		$last_names = array_column($myarry,'price_heji');
//		array_multisort($last_names,SORT_DESC,$myarry);
        foreach ($total as $k=>$v){
            $myarry[$k]['biaoshi_xian'] = $k;
            $myarry[$k]['price_heji'] = $v;
            $myarry[$k]['tuihuo_heji'] = $tuihuo_total[$k];
            $myarry[$k]['heji'] = $v - $tuihuo_total[$k];
            $price_heji+=$v - $tuihuo_total[$k];
        }
        $myarry = array_values($myarry);
		$this->assign('price_heji',$price_heji);
		$this->assign('list',$myarry);
		$this->display();
	}
	
	public function youke(){
		$p = I('p',1);
		$start = ($p-1)*20;
		$mobile = I('mobile');
		$kehu = I('kehu');
		if(empty(I('timegap')))
		$this->begin=strtotime(date('Y-m-d 00:00'));
		
		$this->assign('timegap',date('Y-m-d H:i',$this->begin).' - '.date('Y-m-d H:i',$this->end));
		
		$where = " ";
		if($mobile){
			$where .=  " and (c.mobile like '%$mobile%' or biaoshi_xian = '$mobile') ";
		}
		if($kehu){
			$where .= " and (c.company like'%$kehu%' or c.nickname like'%$kehu%') ";
		}
		if(session('fenbu_id')!=0){
			$where=' and c.fenbu_id='.\session('fenbu_id');
		}
		$res = M()->cache(true,TPSHOP_CACHE_TIME)->query("select a.*,b.goods_name,c.company,c.mobile,c.nickname,c.biaoshi_xian from __PREFIX__goods_liulan a INNER join __PREFIX__goods b on a.goods_id=b.goods_id INNER join __PREFIX__users c on a.user_id=c.user_id where a.add_time>$this->begin and a.add_time<$this->end $where order by a.liulan_id DESC limit $start,20");
		
		$total = M()->cache(true,TPSHOP_CACHE_TIME)->query("select a.*,b.goods_name,c.company,c.mobile,c.nickname,c.biaoshi_xian from __PREFIX__goods_liulan a INNER join __PREFIX__goods b on a.goods_id=b.goods_id INNER join __PREFIX__users c on a.user_id=c.user_id where a.add_time>$this->begin and a.add_time<$this->end $where");
		
		$count = \count($total);
		$Page = new \Think\Page($count,20);
		$show = $Page->show();
		$this->assign('page',$show);
		$this->assign('heji',$count);
		$this->assign('list',$res);
		$this->display();
	}
	public function exportyouke(){
	    $mobile = I('mobile');
		$kehu = I('kehu');
		if(empty(I('timegap')))
		$this->begin=strtotime(date('Y-m-d 00:00'));
		
		$this->assign('timegap',date('Y-m-d H:i',$this->begin).' - '.date('Y-m-d H:i',$this->end));
		
		$where = " ";
		if($mobile){
			$where .=  " and (c.mobile like '%$mobile%' or biaoshi_xian = '$mobile') ";
		}
		if($kehu){
			$where .= " and (c.company like'%$kehu%' or c.nickname like'%$kehu%') ";
		}
		if(session('fenbu_id')!=0){
			$where=' and c.fenbu_id='.\session('fenbu_id');
		}
		$res = M()->cache(true,TPSHOP_CACHE_TIME)->query("select a.*,b.goods_name,c.company,c.mobile,c.nickname,c.biaoshi_xian from __PREFIX__goods_liulan a INNER join __PREFIX__goods b on a.goods_id=b.goods_id INNER join __PREFIX__users c on a.user_id=c.user_id where a.add_time>$this->begin and a.add_time<$this->end $where order by a.liulan_id DESC");
		
		
		        $strTable ='<table width="500" border="1">';
    	$strTable .= '<tr>';
		$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">序号</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">名称 / 公司</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">线路标识</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">联系电话</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">访问产品</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">访问时间</td>';
    	$strTable .= '</tr>';
    	foreach($res as $k=>$val){
		    $i = $k+1;
    		$strTable .= '<tr>';
			$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$i.'</td>';
    		$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['nickname'].'/('.$val['company'].')</td>';
			$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['biaoshi_xian'].'</td>';
			$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['mobile'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['goods_name'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.date('Y-m-d H:i:s',$val['add_time']).' </td>';
    		$strTable .= '</tr>';
    	}
    	$strTable .='</table>';
    	unset($res);
    	downloadExcel($strTable,'产品访问');
    	exit();
	}

    public function userTop_no()
    {
        $p = I('p', 1);
        $limit = 20;
        $start = ($p - 1) * $limit;
        $mobile = I('mobile');
        $kehu = I('kehu');
        $qiyong = I('qiyong');

        $where = "1=1";
        if ($mobile) {
            $where .=  " and (mobile like '%$mobile%' or biaoshi_xian = '$mobile') ";
        }
        if ($kehu) {
            $where .= " and (company like'%$kehu%' or nickname like'%$kehu%') ";
        }

        // 查询用户列表
        $users = M('users')
            ->field('user_id,biaoshi_xian,biaoshi,company,nickname,mobile,fenbu_id,reg_time,company_address')
            ->where($where)
            ->select();

        $validUserIds = [];
        // 批量获取有订单的用户ID
        $orderUserIds = M('order')
            ->where('order_status in (0,1,2,4) and add_time >'.$this->begin.' and  add_time <'.$this->end)
            ->group('user_id')
            ->getField('user_id', true);
//        $orderUserIds = array_flip(array_flip($orderUserIds)); // 去重

        // 批量获取开店的用户ID
        $storeUserIds = M('store')
            ->group('user_id')
            ->getField('user_id', true);
//        $storeUserIds = array_flip(array_flip($storeUserIds)); // 去重

        foreach ($users as $v) {
            if (!in_array($v['user_id'], $orderUserIds) && !in_array($v['user_id'], $storeUserIds)) {
                $validUserIds[] = $v['user_id'];
            }
        }

        // 重新查询符合条件的用户并分页
        $whereIn = implode(',', $validUserIds);
        if ($whereIn) {
            $res = M('users')
                ->field('user_id,biaoshi_xian,biaoshi,company,nickname,mobile,fenbu_id,reg_time,company_address')
                ->where("user_id IN ($whereIn)")
                ->limit($start, $limit)
                ->select();
            $count = count($validUserIds);
        } else {
            $res = [];
            $count = 0;
        }

        $Page = new \Think\Page($count, $limit);
        $show = $Page->show();
        $this->assign('page', $show);
        $this->assign('heji', $count);
        $this->assign('list', $res);
        $this->display();
    }

    public function userTop_no_export(){

        $p = I('p', 1);
        $limit = 20;
        $start = ($p - 1) * $limit;
        $mobile = I('mobile');
        $kehu = I('kehu');
        $qiyong = I('qiyong');

        $where = "1=1";
        if ($mobile) {
            $where .=  " and (mobile like '%$mobile%' or biaoshi_xian = '$mobile') ";
        }
        if ($kehu) {
            $where .= " and (company like'%$kehu%' or nickname like'%$kehu%') ";
        }

        // 查询用户列表
        $users = M('users')
            ->field('user_id,biaoshi_xian,biaoshi,company,nickname,mobile,fenbu_id,reg_time,company_address')
            ->where($where)
            ->select();

        $validUserIds = [];
        // 批量获取有订单的用户ID
        $orderUserIds = M('order')
            ->where('order_status in (0,1,2,4) and add_time >'.$this->begin.' and  add_time <'.$this->end)
            ->getField('user_id', true);

        $orderUserIds = array_flip(array_flip($orderUserIds)); // 去重

        // 批量获取开店的用户ID
        $storeUserIds = M('store')
            ->getField('user_id', true);
        $storeUserIds = array_flip(array_flip($storeUserIds)); // 去重

        foreach ($users as $v) {
            if (!in_array($v['user_id'], $orderUserIds) && !in_array($v['user_id'], $storeUserIds)) {
                $validUserIds[] = $v['user_id'];
            }
        }
        $whereIn = implode(',', $validUserIds);

        $total = M('users')
            ->field('user_id,biaoshi_xian,biaoshi,company,nickname,mobile,fenbu_id,reg_time,company_address')
            ->where("user_id IN ($whereIn)")
            ->select();


        $strTable ='<table width="500" border="1">';
        $strTable .= '<tr>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:120px;">用户id</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="160">会员标识</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">线路标识</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">客户名</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">联系电话</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">客户地址</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">注册时间</td>';
        $strTable .= '</tr>';

        foreach($total as $k=>$val){
            $p++;
            $strTable .= '<tr>';
            $strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['user_id'].'</td>';
            $strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['biaoshi'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">&nbsp;'.$val['biaoshi_xian'].' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['nickname'].'('.$val['company'].')</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['mobile'].' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['company_address'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.date("Y-m-d H:i:s",$val['reg_time']).'</td>';
            $strTable .= '</tr>';
        }

        $strTable .='</table>';
        unset($list);
        downloadExcel($strTable,'没消费会员');
        exit();
    }
	
	public function salelist_export(){
		$kehu= trim(I('kehu'));
		//$begin=str_replace('+', ' ', I('begin'));$end=str_replace('+', ' ', I('end'));
		
		$heji=0;
	    $fenbu_id = I('fenbu_id');
// 		$fenbu_id=I('fenbu_id') ? I('fenbu_id') : \session('fenbu_id');
		
// 		$fenbu_ids=$fenbu_id ? $fenbu_id : '0,31,32,33,34,37,42';
        $fenbu_ids = $fenbu_id;
        if($fenbu_id == ''){
             $fenbu = M('fenbu')->field('fenbu_id')->select();
            $fenbu_ids = '';
            foreach ($fenbu as $v){
                $fenbu_ids.= $v['fenbu_id'].',';
            }
            $fenbu_ids = substr($fenbu_ids,0,-1);
            // $fenbu_ids = '0,31,32,33,34,37,42';
        }
		//店铺查询趋势 废除store_moneys 
		//$count=M()->query("SELECT a.store_id from ty_store a where store_name like '%$kehu%' order by a.store_id desc");
		$count=M('store')->where("store_name like '%$kehu%'")->getField('store_id',true);
		if($kehu && $count){
			$where="a.add_time>$this->begin and a.add_time<$this->end and ufenbu_id in($fenbu_ids) and (order_status in(0,1,2)) and b.store_name like '%$kehu%'";
		}else{
			$where="a.add_time>$this->begin and a.add_time<$this->end and a.ufenbu_id in($fenbu_ids) and a.order_status in(0,1,2)";
		
		}
		
		$total = M('order a') //店铺查询趋势 shadow
		->field("sum(a.order_amount) xiaoshoue,a.ufenbu_id,b.store_id,b.store_name,b.store_phone")
		->join("ty_store b on a.store_id=b.store_id")
		->where($where)
		->group("a.store_id")
		->order("xiaoshoue desc")
		->select();
		// dump($total);
        $strTable ='<table width="500" border="1">';
		$strTable .= '<tr>';
		$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">id</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="160">店铺名</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">联系电话</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">订单数</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">下单人数</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">销售金额</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">平台扣点</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">扣点后金额</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">出售日期</td>';
		$strTable .= '</tr>';
		foreach($total as $key=>$val){
			$order_list= M()->query("select count(order_id) danshu,sum(kou_price) kou_price from ty_order where add_time>$this->begin and add_time<$this->end and store_id=".$val['store_id']." and order_status in(0,1,2)");
			$val['danshu']=$order_list[0]['danshu']; //单数
			$val['renshu']=count(M('order')->field('order_id')->where("add_time>$this->begin and add_time<$this->end and store_id=".$val['store_id']." and order_status in(0,1,2)")->group('user_id')->select()); //人数
			
			$val['kou_price']=$order_list[0]['kou_price']; //扣点收入
			$price = $val['xiaoshoue']-$val['kou_price'];
			$strTable .= '<tr>';
			$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['store_id'].'</td>';
			$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['store_name'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">&nbsp;'.$val['store_phone'].' </td>';
		    $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['danshu'].'</td>';
		    $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['renshu'].' </td>';
		    $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['xiaoshoue'].'</td>';
		    $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['kou_price'].'</td>';
		    $strTable .= '<td style="text-align:left;font-size:12px;">'.$price.'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.date("Y-m-d H:i:s",$this->begin).'~'.date("Y-m-d H:i:s",$this->end).'</td>';
			$strTable .= '</tr>';
			
		}
		$strTable .='</table>';
		unset($list);
		downloadExcel($strTable,'商家销售排行');
		exit();
	}
	
	public function saleList(){
		$p = I('p',1);
		$start = ($p-1)*16;
		$cat_id = I('cat_id',0);
		$brand_id = I('brand_id',0);
		$kehu= trim(I('kehu'));
		//$begin=str_replace('+', ' ', I('begin'));$end=str_replace('+', ' ', I('end'));
		
		$heji=0;
	    $fenbu_id = I('fenbu_id');
// 		$fenbu_id=I('fenbu_id') ? I('fenbu_id') : \session('fenbu_id');
		
// 		$fenbu_ids=$fenbu_id ? $fenbu_id : '0,31,32,33,34,37,42';
        $fenbu_ids = $fenbu_id;
        if($fenbu_id == ''){
             $fenbu = M('fenbu')->field('fenbu_id')->select();
            $fenbu_ids = '';
            foreach ($fenbu as $v){
                $fenbu_ids.= $v['fenbu_id'].',';
            }
            $fenbu_ids = substr($fenbu_ids,0,-1);
            // $fenbu_ids = '0,31,32,33,34,37,42';
        }
		//店铺查询趋势 废除store_moneys 
		//$count=M()->query("SELECT a.store_id from ty_store a where store_name like '%$kehu%' order by a.store_id desc");
		$count=M('store')->where("store_name like '%$kehu%'")->getField('store_id',true);
		
		if($kehu && $count){
			$where="a.add_time>=$this->begin and a.add_time<=$this->end and ufenbu_id in($fenbu_ids) and (order_status in(0,1,2)) and b.store_name like '%$kehu%'";
			$hejis=M('order')->field('sum(order_amount) as heji,sum(kou_price) as kou')->where("add_time>=$this->begin and add_time<=$this->end and ufenbu_id in($fenbu_ids) and order_status in(0,1,2) and store_id in(".join(',',$count).")")->select(); //销售额合计 shadow.$count[0]['store_id']
		
		}else{
			$where="a.add_time>=$this->begin and a.add_time<=$this->end and a.ufenbu_id in($fenbu_ids) and a.order_status in(0,1,2)";
			$hejis=M('order')->field('sum(order_amount) as heji,sum(kou_price) as kou')->where("add_time>=$this->begin and add_time<=$this->end and (order_status in(0,1,2)) and ufenbu_id in($fenbu_ids)")->select(); //销售额合计 shadow
		}
		
		$total = M('order a') //店铺查询趋势 shadow
		->field("sum(a.order_amount) xiaoshoue,a.ufenbu_id,b.store_id,b.store_name,b.store_phone")
		->join("ty_store b on a.store_id=b.store_id")
		->where($where)
		->group("a.store_id")
		->order("xiaoshoue desc")
		->limit($start,16)
		->select();

		foreach($total as $key=>$v){
// 			$order_list= M()->query("select count(o.order_id) danshu,sum(g.member_goods_price*g.goods_num*(g.points/100)) kou_price from ty_order o left join ty_order_goods g on g.order_id = o.order_id where o.add_time>=$this->begin and o.add_time<=$this->end and o.store_id=".$v['store_id']." and o.order_status in(0,1,2)");
			$order_list = M('order o')->field("count(o.order_id) danshu,sum(((g.member_goods_price+g.yijia)*g.goods_num)*(g.points/100)) as kou_price")
			->join('ty_order_goods g on g.order_id = o.order_id','left')
			->where("o.add_time>=$this->begin and o.add_time<=$this->end and o.store_id=".$v['store_id']." and o.order_status in(0,1,2)")
			->select();
// 			dump($order_list);
			$total[$key]['danshu']=M('order')->where("add_time>=$this->begin and add_time<=$this->end and store_id=".$v['store_id']." and order_status in(0,1,2)")->count(); //单数
			$total[$key]['renshu']=count(M('order')->field('order_id')->where("add_time>=$this->begin and add_time<=$this->end and store_id=".$v['store_id']." and order_status in(0,1,2)")->group('user_id')->select()); //人数
			
			$total[$key]['kou_price']=geshi($order_list[0]['kou_price']); //扣点收入
			
		}

		$Page = new \Think\Page(\count($count),16);
		$show = $Page->show();
		$this->assign('page',$show);
		$this->assign('fenbu_list',$this->fenbu_list);

		$this->assign([
			'list'=>$total,
			'heji'=>$hejis[0]['heji'],
			'heji_k'=>$hejis[0]['kou'],
			'begin'=>$this->begin,
			'end'=>$this->end
		]);
		
        // $GoodsLogic = new GoodsLogic();
        // $brandList = $GoodsLogic->getSortBrands();
        // $categoryList = $GoodsLogic->getSortCategory();
        // $this->assign('categoryList',$categoryList);
        //$this->assign('brandList',$brandList);
		$this->display();
	}
	
	public function user(){
		$today = strtotime(date('Y-m-d'));
		$month = strtotime(date('Y-m-01'));
		$user['today'] = D('users')->where("reg_time>$today")->count();//今日新增会员
		$user['month'] = D('users')->where("reg_time>$month")->count();//本月新增会员
		$user['total'] = D('users')->count();//会员总数
		$user['tuihuo'] = D('return_goods')->count();//退货总数
		$user['daike'] = D('order')->where('admin_note="商家代客添加订单。"')->count();//退货总数
		$res = M('order')->cache(true)->distinct(true)->field('user_id')->select();
		$user['hasorder'] = count($res);
		$user['noorder'] = $user['total']-$user['hasorder'];//无订单用户
		$this->assign('user',$user);
		if(session('fenbu_id')!=0){
			$wheres=' and fenbu_id='.\session('fenbu_id');
		}
		$sql = "SELECT COUNT(*) as num,FROM_UNIXTIME(reg_time,'%Y-%m-%d') as gap from __PREFIX__users where reg_time>$this->begin and reg_time<$this->end $wheres group by gap";
		$new = M()->query($sql);//新增会员趋势		
		foreach ($new as $val){
			$arr[$val['gap']] = $val['num'];
		}
		
		for($i=$this->begin;$i<=$this->end;$i=$i+24*3600){
			$brr[] = empty($arr[date('Y-m-d',$i)]) ? 0 : $arr[date('Y-m-d',$i)];
			$day[] = date('Y-m-d',$i);
		}		
		$result = array('data'=>$brr,'time'=>$day);
		$this->assign('result',json_encode($result));					
		$this->display();
	}
	
	//财务统计
	public function finance(){
		$sql = "SELECT sum(b.goods_num*b.member_goods_price) as goods_amount,sum(a.shipping_price) as shipping_amount,sum(b.goods_num*b.cost_price) as cost_price,";
		$sql .= "sum(a.coupon_price) as coupon_amount,FROM_UNIXTIME(a.add_time,'%Y-%m-%d') as gap from  __PREFIX__order a left join __PREFIX__order_goods b on a.order_id=b.order_id ";
		$sql .= " where a.add_time>$this->begin and a.add_time<$this->end AND a.pay_status=1 and a.shipping_status=1 and b.is_send=1 group by gap order by a.add_time";
		$res = M()->cache(true)->query($sql);//物流费,交易额,成本价
		
		foreach ($res as $val){
			$arr[$val['gap']] = $val['goods_amount'];
			$brr[$val['gap']] = $val['cost_price'];
			$crr[$val['gap']] = $val['shipping_amount'];
			$drr[$val['gap']] = $val['coupon_amount'];
		}
			
		for($i=$this->begin;$i<=$this->end;$i=$i+24*3600){
			$date = $day[] = date('Y-m-d',$i);
			$tmp_goods_amount = empty($arr[$date]) ? 0 : $arr[$date];
			$tmp_cost_amount = empty($brr[$date]) ? 0 : $brr[$date];
			$tmp_shipping_amount = empty($crr[$date]) ? 0 : $crr[$date];
			$tmp_coupon_amount = empty($drr[$date]) ? 0 : $drr[$date];
			
			$goods_arr[] = $tmp_goods_amount;
			$cost_arr[] = $tmp_cost_amount;
			$shipping_arr[] = $tmp_shipping_amount;
			$coupon_arr[] = $tmp_coupon_amount;
			$list[] = array('day'=>$date,'goods_amount'=>$tmp_goods_amount,'cost_amount'=>$tmp_cost_amount,
					'shipping_amount'=>$tmp_shipping_amount,'coupon_amount'=>$tmp_coupon_amount,'end'=>date('Y-m-d',$i+24*60*60));
		}
		$this->assign('list',$list);
		$result = array('goods_arr'=>$goods_arr,'cost_arr'=>$cost_arr,'shipping_arr'=>$shipping_arr,'coupon_arr'=>$coupon_arr,'time'=>$day);
		$this->assign('result',json_encode($result));
		$this->display();
	}

    public function shop_yeji()
    {
		$today = strtotime(date('Y-m-d'));
		$qiday = strtotime(date('Y-m-d',strtotime("-7 day")));
		$month = strtotime(date('Y-m-01'));
		$where = "a.order_status in(0,1,2,4)";
		$stores=M('store a')
				->field('a.store_id,a.store_name,
				(select sum(order_amount) from ty_order where order_status in(0,1,2,4) and add_time>'.$today.' and store_id=a.store_id) as jinri_price,
				(select sum(order_amount) from ty_order where order_status in(0,1,2,4) and add_time>'.$qiday.' and add_time<'.$today.' and store_id=a.store_id) as qi_price,
				(select sum(order_amount) from ty_order where order_status in(0,1,2,4) and add_time>'.$month.' and store_id=a.store_id) as yue_price,
				(select sum(order_amount) from ty_order where order_status in(0,1,2,4) and store_id=a.store_id) as zong_price,
				(select count(*) from ty_order where order_status in(0,1,2,4) and store_id=a.store_id) as danshu
				')
				->select();
		// echo \json_encode($stores);
		// exit;
		
    	$strTable ='<table width="500" border="1">';
    	$strTable .= '<tr>';
    	$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">店铺名</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">今日销售额</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">七日销售额</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">月销售额</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="100">订单数</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">总销售额</td>';
    	$strTable .= '</tr>';
    	

    	foreach($stores as $k=>$val){
    		$strTable .= '<tr>';
    		$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['store_name'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['jinri_price'].' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['qi_price'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['yue_price'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['danshu'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['zong_price'].'</td>';
    		$strTable .= '</tr>';
    	}
    	$strTable .='</table>';
    	unset($orderList);
    	downloadExcel($strTable,'order');
    	exit();
    }

    public function export_user()
    {$mobile = I('mobile');
		$kehu = I('kehu');
		$order_type=I('order_type');
        $company_address = I('company_address');
		if($order_type!=0){
            if($order_type=='1'){
                $where = "a.dai_type = 0 and ";
            }elseif($order_type=='2'){
                $where = "a.dai_type = 1 and ";
            }
		}
		$where .= " a.order_status in(0,1,2,4)";
        $whereb="a.addtime>$this->begin and a.addtime<$this->end and b.is_lock=0";
        if(I('fenbu_id')!=''){
            $fenbu_id=I('fenbu_id');
            $where .= " and b.fenbu_id=$fenbu_id";
            $whereb .= " and b.fenbu_id=$fenbu_id";
        }
        if($mobile){
			//$where .=  " and (b.mobile like '%$mobile%' or b.biaoshi_xian = '$mobile') ";
//			$where .=  " and b.biaoshi_xian = '$mobile' ";
            $where .=  " and b.biaoshi_xian = '$mobile' ";
            $whereb.=" and b.biaoshi_xian = '$mobile' ";
		}
        if($company_address){
            $where .=  " and b.company_address like '%$company_address%' ";
            $whereb.=" and b.company_address like '%$company_address%' ";
        }
		if($kehu){
            $where .= " and (b.company like'%$kehu%' or b.nickname like'%$kehu%') ";
            $whereb.=" and (b.company like'%$kehu%' or b.nickname like'%$kehu%') ";
        }
		$whereb .= " and a.status!=3";
//		if(I('fenbu_id')!=''){
//			$fenbu_id=I('fenbu_id');
//			$where .= " and b.fenbu_id=$fenbu_id";
//		}
// 		echo $where;
// 		die;
		$sql = "select a.add_time,count(a.order_id) as order_num,sum(a.total_amount+yijia_heji) as amount,a.user_id,b.mobile,b.company,b.nickname,b.user_id,b.biaoshi,b.biaoshi_xian,b.company_address from __PREFIX__order as a left join __PREFIX__users as b on a.user_id = b.user_id where a.add_time>$this->begin and a.add_time<$this->end and $where group by a.user_id order by amount DESC";
		//$res = M()->cache(true)->query($sql);//echo \json_encode($res);
		
    	$orderList = D()->query($sql);
    
    	$strTable ='<table width="500" border="1">';
    	$strTable .= '<tr>';
    	$strTable .= '<td style="text-align:center;font-size:12px;width:120px;">用户ID</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="100">排行</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">客户名</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">货号标识</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">标识线路</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">联系电话</td>';
		$strTable .= '<td style="text-align:center;font-size:12px;" width="*">客户地址</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">订单数</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">购物金额</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">退货金额</td>';
    	$strTable .= '<td style="text-align:center;font-size:12px;" width="*">实际金额</td>';
    	$strTable .= '</tr>';
    	
     
    	foreach($orderList as $k=>$val){
    	    
            // $whereb .= ' and a.user_id = '.$val['user_id'].' and a.status = 2';
            $userInfo = M('users')->where(['user_id'=>$val['user_id']])->find();
            $return_goods_price = 0;
            if(!empty($userInfo)){
                $tuikuan2=M('return_goods a')
                ->field("a.member_goods_price,a.goods_num")
                ->join('ty_users b on a.user_id=b.user_id')
                ->where($whereb.' and a.user_id = '.$val['user_id'])
                ->select();
                
                if(count($tuikuan2) > 0){
                    foreach ($tuikuan2 as $key => $value){
                        $return_goods_price += ($value['member_goods_price']*$value['goods_num']);
                    }
                }
            }
            
                
               $val['return_goods_price'] = $return_goods_price;
            
            
            
    		$strTable .= '<tr>';
    		$strTable .= '<td style="text-align:center;font-size:12px;">&nbsp;'.$val['user_id'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.($k+1).' </td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['nickname'].'（'.$val['company'].'）'.' </td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['biaoshi'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['biaoshi_xian'].'</td>';
            $strTable .= '<td style="text-align:left;font-size:12px;">'.$val['mobile'].'</td>';
			$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['company_address'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['order_num'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['amount'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.$val['return_goods_price'].'</td>';
    		$strTable .= '<td style="text-align:left;font-size:12px;">'.geshi($val['amount']-$val['return_goods_price']).'</td>';
    		$strTable .= '</tr>';
    	}
    	$strTable .='</table>';
    	unset($orderList);
    	downloadExcel($strTable,'order');
    	exit();
    }

    public function export_userTop_xian()
    {
        $myarry=[];$price_heji=0;
		if(session('fenbu_id')!=0){
			$wheres=' and b.fenbu_id='.\session('fenbu_id');
		}

        function group_same_key_sofwu($arr,$key){
            $new_arr = array();
            foreach($arr as $k=>$v ){
                $new_arr[$v[$key]] +=$v['amount'];
            }
            return $new_arr;
        }

        $total = M()->query("select (a.order_amount+a.yijia_heji) as amount, b.biaoshi_xian from __PREFIX__order as a left join __PREFIX__users as b on a.user_id = b.user_id where  a.chuku_last_time>$this->begin and a.chuku_last_time<$this->end and a.order_status in(0,1,2,4)  $wheres");
        $total = group_same_key_sofwu($total,'biaoshi_xian');
        ksort($total, SORT_NATURAL);


        $tuihuo_total = M()->query("select ROUND(((a.member_goods_price+a.yijia) * a.goods_num),3) as amount,b.biaoshi_xian from __PREFIX__return_goods as a left join __PREFIX__users as b on a.user_id = b.user_id where  a.addtime>$this->begin and a.addtime<$this->end  $wheres");
        $tuihuo_total = group_same_key_sofwu($tuihuo_total,'biaoshi_xian');
        ksort($tuihuo_total, SORT_NATURAL);

        foreach ($total as $k=>$v){
            $myarry[$k]['biaoshi_xian'] = $k;
            $myarry[$k]['price_heji'] = $v;
            $myarry[$k]['tuihuo_heji'] = $tuihuo_total[$k];
            $myarry[$k]['heji'] = $v - $tuihuo_total[$k];
        }
        $myarry = array_values($myarry);
       
        $e_name='联鑫壹天达五金商城';
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Worksheet.Drawing");
        import("Org.Util.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new \PHPExcel();
        $objActSheet = $objPHPExcel->getActiveSheet();

        $objActSheet->setCellValue('A1', '线路标识');
        $objActSheet->setCellValue('B1', '总金额');
        $objActSheet->setCellValue('C1', '退货抵扣');
        $objActSheet->setCellValue('D1', '实收合计');

        $objActSheet->getDefaultRowDimension()->setRowHeight(25);
        $objActSheet->getDefaultColumnDimension()->setWidth(25);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        $i=2;


        foreach($myarry as $item){
            $objActSheet->setCellValue('A'.$i, $item['biaoshi_xian']);
            $objActSheet->setCellValue('B'.$i, $item['price_heji']);
            $objActSheet->setCellValue('C'.$i, $item['tuihuo_heji']);
            $objActSheet->setCellValue('D'.$i, $item['heji']);
            $i++;
        }

        // 设置单元格边框
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),

            ),
        );

        $objPHPExcel->getActiveSheet()->getStyle( 'A1:D'.($i-1))->applyFromArray($styleThinBlackBorderOutline);

        ob_end_clean();
        $fileName = $e_name.'线路消费统计';
        $date = date("Y-m-d",time());
        $fileName .= "_{$date}.xls";
        //重命名表
        $objPHPExcel->getActiveSheet()->setTitle('viateams');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');


        ob_start();
        $objWriter->save("php://output");
        $xlsdata = ob_get_contents();
        ob_end_clean();
        $data=['filename' => $fileName, 'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsdata)];
        echo json_encode($data);
        exit;
    }
}